CREATE VIEW dbo.vCampaignMgmtReport
AS
SELECT     C.Name AS Campaign, C.StartDate AS CampaignStartDate, C.EndDate AS CampaignEndDate, C.CampaignStatusCode,
                      C.TargetRevenue AS CampaignTargetRevenue, C.PredictedResponseRate AS CampaignPredictedResponseRate,
                      C.TotalRevenue AS CampaignTotalRevenue, A.Name AS Appeal, A.AppealStatusCode, A.TotalRevenue AS AppealTotalRevenue,
                      A.TargetRevenue AS AppealTargetRevenue, A.PredictedResponseRate AS AppealPredictedResponseRate, A.StartDate AS AppealStartDate,
                      A.EndDate AS AppealEndDate, ASR.AppealStatusName, S.Name AS Solicitation, S.SolicitationStatusCode,
                      S.TargetRevenue AS SolicitationTargetRevenue, S.TotalRevenue AS SolicitationTotalRevenue,
                      S.PredictedResponseRate AS SolicitationPredictedResponseRate, S.StartDate AS SolicitationStartDate, S.EndDate AS SolicitationEndDate,
                      S.ReminderDate AS SolicitationReminderDate, SSR.SolicitationStatusName, CSR.CampaignStatusName, SC.Code AS SourceCode,
                      SC.Name AS SourceCodeName, SC.OverheadCost, SC.EstimatedCost, SC.TargetRevenue AS SourceCodeTargetRevenue,
                      SC.TotalRevenue AS SourceCodeTotalRevenue, SC.TotalSolicited AS SourceCodeTotalSolicited,
                      SC.PredictedResponseRate AS SourceCodePredictedResponseRate, SC.ExternalListCount, SC.ResponsesTotalledOn, SC.LastDropDate,
                      SC.FirstResponseDate, SC.LastResponseDate, SC.LowResponseAmount, SC.HighResponseAmount, SC.TotalPositiveResponse,
                      SC.TotalNegativeResponse, SCSR.SourceCodeStatusName, SCTR.SourceCodeTypeName, C.CampaignKey, A.AppealKey, S.SolicitationKey,
                      SC.SourceCodeKey, C.SystemEntityKey, C.CampaignTypeCode
FROM         dbo.CampaignMain AS C INNER JOIN
                      dbo.CampaignStatusRef AS CSR ON C.CampaignStatusCode = CSR.CampaignStatusCode LEFT OUTER JOIN
                      dbo.AppealMain AS A ON C.CampaignKey = A.CampaignKey LEFT OUTER JOIN
                      dbo.AppealStatusRef AS ASR ON A.AppealStatusCode = ASR.AppealStatusCode LEFT OUTER JOIN
                      dbo.SolicitationMain AS S ON A.AppealKey = S.AppealKey LEFT OUTER JOIN
                      dbo.SolicitationStatusRef AS SSR ON S.SolicitationStatusCode = SSR.SolicitationStatusCode LEFT OUTER JOIN
                      dbo.SourceCode AS SC ON S.SolicitationKey = SC.SolicitationKey LEFT OUTER JOIN
                      dbo.SourceCodeStatusRef AS SCSR ON SC.SourceCodeStatusCode = SCSR.SourceCodeStatusCode LEFT OUTER JOIN
                      dbo.SourceCodeTypeRef AS SCTR ON SC.SourceCodeTypeCode = SCTR.SourceCodeTypeCode

GO